<?php

/*
 * big report calculation
 */

// Retrieving SRs and substatuses.
$query = 'select
        tse.team,
        tse.tse_name,
        srs.rca,
        srs.techlead,
        sr,
        srsubstatus.val as substatus
    from tse, srs, srsubstatus
    where
        tse.owner_id=srs.owner and
        srs.sub_status=srsubstatus.id and
        srs.owner in (' . $ids . ')
        and srs.closed >=' . $from . ' and
        srs.closed <=' . $till . '
    order by tse.team, tse.tse_name, substatus';
@ $result = mysql_query($query);

while (@ $res = mysql_fetch_assoc($result)) {
    $big[$res['team']][$res['tse_name']][$res['sr']]['ss'] = $res['substatus'];
    if ($res['rca'] == 'Y')
        $big[$res['team']][$res['tse_name']][$res['sr']]['rca'] = '+';
    else $big[$res['team']][$res['tse_name']][$res['sr']]['rca'] = 'NO';
        
    
    if ($res['techlead'] == 'Y')
        $big[$res['team']][$res['tse_name']][$res['sr']]['techlead'] = '+';
}
@ mysql_free_result($result);


// retrieving srs with Tier-3 hands off
$query = 'select
        tse.team,
        tse.tse_name,
        sr
    from tse, srs
    where
        srs.tier_t_owner <> \'\' and
        tse.owner_id=srs.owner and
        srs.owner in (' . $ids . ') and
        srs.closed >=' . $from . ' and
        srs.closed <=' . $till . '
    order by tse.team, tse.tse_name';
@ $result = mysql_query($query);

while (@ $res = mysql_fetch_assoc($result))
    $big[$res['team']][$res['tse_name']][$res['sr']]['tier3'] = 'T3';
@ mysql_free_result($result);

// Retrieving KCS linked

$query = 'select
        tse.team,
        tse.tse_name,
        kcs.sr,
        kcs.type,
        kcs.usefulness,
        srs.owner,
        kcs.linked_by
    from tse, kcs, srs
    where
        srs.sr = kcs.sr and
        tse.owner_id = srs.owner and
        srs.owner in (' . $ids . ') and
        srs.closed >= ' . $from . ' and
        srs.closed <= ' . $till . '
    order by tse.team, tse.tse_name, sr;';

@ $result = mysql_query($query);

while (@ $res = mysql_fetch_assoc($result)) {
    $big[$res['team']][$res['tse_name']][$res['sr']][$res['type']]++;
    if ($res['usefulness'] == 'N')
        $big[$res['team']][$res['tse_name']][$res['sr']]['N']++;
    if ($res['owner'] <> $res['linked_by'])
        $big[$res['team']][$res['tse_name']][$res['sr']]['O']++;;
}
@ mysql_free_result($result);

// Retrieveing average surveys.
$query = 'select
      tse.team,
      tse.tse_name,
      custsat.sr,
      cast(avg(skill) AS char(4)) as skill,
      cast(avg(advised) as char(4)) as advised,
      cast(avg(professional) as char(4)) as prof,
      cast(avg(time) as char(4)) as time,
      cast(avg(efect) as char(4)) as efect,
      cast(avg(overall) as char(4)) as overall
    from tse, custsat, srs
    where
      srs.sr = custsat.sr and
      tse.owner_id=srs.owner and
      srs.owner in (' . $ids . ') and
      srs.closed >=' . $from . ' and
      srs.closed <=' . $till . '
    group by sr
    order by tse.team, tse.tse_name, sr;';
@ $result = mysql_query($query);

while (@ $res = mysql_fetch_assoc($result)) {
    $big[$res['team']][$res['tse_name']][$res['sr']]['skill'] = $res['skill'];
    $big[$res['team']][$res['tse_name']][$res['sr']]['advised'] = $res['advised'];
    $big[$res['team']][$res['tse_name']][$res['sr']]['prof'] = $res['prof'];
    $big[$res['team']][$res['tse_name']][$res['sr']]['time'] = $res['time'];
    $big[$res['team']][$res['tse_name']][$res['sr']]['efect'] = $res['efect'];
    $big[$res['team']][$res['tse_name']][$res['sr']]['overall'] = $res['overall'];
}
@ mysql_free_result($result);


//echo 'Full report <pre>';print_r($big);echo'</pre>';
?>
